# 第六章 ： 多表连接查询

```mysql
create database one20210605 default charset=utf8;

use one20210605;

create table student (
	sid int ,							-- 学生id
	`sname` varchar(10) ,	-- 学生姓名
	saddress varchar(100) --  学生地址
);

create table course (
	cid int ,							-- 课程id
	sid int ,							-- 选课的学生id
	cnane varchar(100)		-- 课程的名字
	
);

insert into student values ( 1 , '关羽' , '许昌' ) , ( 2 , "诸葛亮" , "南阳") , ( 3 , "张飞" , "河北") , ( 4 , "刘备"  , "四川") , ( 5 , "阿斗" , "蜀国") ;

insert into course values (101 , 1 , "三百回合不累" ) , ( 102 , 2  ,"劝人投降话术") , (101 , 3 , "三百回合不累") , ( 102 , 4 , "劝人投降话术") , (103 , 6 , "如何俘获大乔和小乔") ;


select * from student;
select * from course;

-- 交叉查询  (笛卡尔积查询)
select * from student , course;
create database one20210605 default charset=utf8;

use one20210605;

create table student (
	sid int ,
	`sname` varchar(10) ,
	saddress varchar(100)
);

create table course (
	cid int ,
	sid int ,
	cnane varchar(100)
	
);

insert into student values ( 1 , '关羽' , '许昌' ) , ( 2 , "诸葛亮" , "南阳") , ( 3 , "张飞" , "河北") , ( 4 , "刘备"  , "四川") , ( 5 , "阿斗" , "蜀国") ;

insert into course values (101 , 1 , "三百回合不累" ) , ( 102 , 2  ,"劝人投降话术") , (101 , 3 , "三百回合不累") , ( 102 , 4 , "劝人投降话术") , (103 , 6 , "如何俘获大乔和小乔") ;


select * from student;
select * from course;

-- 交叉连接
select * from student , course;
select * from student , course where student.sid = course.sid;

-- 内连接
select * from student inner join course on student.sid=course.sid;

-- 左外连接
select * from student left join course on student.sid = course.sid ;

 -- 右外连接
select * from student right join course on student.sid = course.sid ;

 -- 全外连接
 select * from student full join course on student.sid = course.sid ;



```

<img src="C:\Users\liyinlong\AppData\Roaming\Typora\typora-user-images\image-20210605181831187.png" alt="image-20210605181831187"  />